﻿Public Class RecetaDAO
    '@Accion as int,
    '@RecetaId as int,
    '@RecetaCodigo as varchar(100),
    '@RecetaFechaPrescripcion as datetime,
    '@RecetaFechaVenta as datetime,
    '@RecetaAfiliadoNumero as varchar(100),
    '@RecetaAfiliadoNombre as varchar(250),
    '@RecetaDiagnostico as varchar(250),
    '@RecetaDatosQuienRetira as text,
    '@MedicoId as int,
    '@EstadoRecetaId as int,
    '@PresentacionId as int

    Public Shared Function insertarReceta(ByVal RecetaCodigo As String, ByVal RecetaFechaPrescripcion As Date, ByVal RecetaFechaVenta As Date, ByVal RecetaAfiliadoNumero As String, ByVal RecetaAfiliadoNombre As String, ByVal RecetaDiagnostico As String, ByVal RecetaDatosQuienRetira As String, ByVal MedicoId As Integer, ByVal PresentacionId As Integer) As Integer
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_receta"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@RecetaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@RecetaCodigo", RecetaCodigo).DbType = DbType.String
                    .AddWithValue("@RecetaFechaPrescripcion", RecetaFechaPrescripcion).DbType = DbType.DateTime
                    .AddWithValue("@RecetaFechaVenta", RecetaFechaVenta).DbType = DbType.DateTime
                    .AddWithValue("@RecetaAfiliadoNumero", RecetaAfiliadoNumero).DbType = DbType.String
                    .AddWithValue("@RecetaAfiliadoNombre", RecetaAfiliadoNombre).DbType = DbType.String
                    .AddWithValue("@RecetaDiagnostico", RecetaDiagnostico).DbType = DbType.String
                    .AddWithValue("@RecetaDatosQuienRetira", RecetaDatosQuienRetira).DbType = DbType.String
                    .AddWithValue("@MedicoId", MedicoId).DbType = DbType.Int32
                    .AddWithValue("@EstadoRecetaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                End With
                .Connection = cn
                Return .ExecuteScalar()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return -1
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarReceta(ByVal RecetaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_receta"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@RecetaId", RecetaId).DbType = DbType.Int32
                    .AddWithValue("@RecetaCodigo", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@RecetaFechaPrescripcion", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@RecetaFechaVenta", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@RecetaAfiliadoNumero", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@RecetaAfiliadoNombre", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@RecetaDiagnostico", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@RecetaDatosQuienRetira", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@MedicoId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@EstadoRecetaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarReceta(ByVal RecetaId As Integer, ByVal RecetaCodigo As String, ByVal RecetaFechaPrescripcion As Date, ByVal RecetaFechaVenta As Date, ByVal RecetaAfiliadoNumero As String, ByVal RecetaAfiliadoNombre As String, ByVal RecetaDiagnostico As String, ByVal RecetaDatosQuienRetira As String, ByVal MedicoId As Integer, ByVal EstadoRecetaId As Integer, ByVal PresentacionId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_receta"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@RecetaId", RecetaId).DbType = DbType.Int32
                    .AddWithValue("@RecetaCodigo", RecetaCodigo).DbType = DbType.String
                    .AddWithValue("@RecetaFechaPrescripcion", RecetaFechaPrescripcion).DbType = DbType.DateTime
                    .AddWithValue("@RecetaFechaVenta", RecetaFechaVenta).DbType = DbType.DateTime
                    .AddWithValue("@RecetaAfiliadoNumero", RecetaAfiliadoNumero).DbType = DbType.String
                    .AddWithValue("@RecetaAfiliadoNombre", RecetaAfiliadoNombre).DbType = DbType.String
                    .AddWithValue("@RecetaDiagnostico", RecetaDiagnostico).DbType = DbType.String
                    .AddWithValue("@RecetaDatosQuienRetira", RecetaDatosQuienRetira).DbType = DbType.String
                    .AddWithValue("@MedicoId", MedicoId).DbType = DbType.Int32
                    .AddWithValue("@EstadoRecetaId", EstadoRecetaId).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerRecetas(ByVal RecetaId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_receta_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@RecetaId", RecetaId).DbType = DbType.Int32
            da.Fill(ds, "Receta")

            Return ds.Tables("Receta")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerRecetasDePresentacion(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_receta_presentacionId", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "Recetas")

            Return ds.Tables("Recetas")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerRecetasDePresentacionNoExcluidas(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_receta_obtenerNoExcluidasXPresentacion", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "Recetas")

            Return ds.Tables("Recetas")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function grillaRecetasDePresentacion(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_receta_grilla", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "Receta")

            Return ds.Tables("Receta")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Sub excluirReceta(ByVal recetaId As Integer)
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_receta_excluir"
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@RecetaId", recetaId).DbType = DbType.Int32
                .Connection = cn
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Sub
End Class
